/*
	Will identify immediate 1:? and m:n relationships 

*/
-- TODO: puzzle: work out the set-based equivalent 

SET NOCOUNT ON


DECLARE @keys TABLE
    --CREATE TABLE #keys
    (
      CONSTRAINT_SCHEMA NVARCHAR(128),
      CONSTRAINT_NAME NVARCHAR(128),
      TABLE_SCHEMA NVARCHAR(128),
      TABLE_NAME NVARCHAR(128),
      COLUMN_NAME NVARCHAR(128),
      ORDINAL_POSITION INT,
      CONSTRAINT_TYPE NVARCHAR(11),
      UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),
      UNIQUE_CONSTRAINT_NAME NVARCHAR(128),
      UNIQUE_TABLE_SCHEMA NVARCHAR(128),
      UNIQUE_TABLE_NAME NVARCHAR(128),
      UNIQUE_COLUMN_NAME NVARCHAR(128),
      UNIQUE_ORDINAL_POSITION INT,
      UNIQUE_CONSTRAINT_TYPE NVARCHAR(11)
    )


BEGIN -- Get a table full of PK and UQ columns
    DECLARE @unique_key_columns TABLE
        (
          -- contains PK and UQ indexes
          [schema_name] NVARCHAR(128),
          table_name NVARCHAR(128),
          index_name NVARCHAR(128),
          column_id INT,
          column_name NVARCHAR(128),
          is_primary_key BIT,
          is_unique_constraint BIT,
          is_unique BIT
        )
    INSERT  INTO @unique_key_columns
            (
              [schema_name],
              table_name,
              index_name,
              column_id,
              column_name,
              is_primary_key,
              is_unique_constraint,
              is_unique
            )
        -- selects PK and UQ indexes
            SELECT  S.name AS [schema_name],
                    T.name AS table_name,
                    IX.name AS index_name,
                    IC.column_id,
                    C.name AS column_name,
                    IX.is_primary_key,
                    IX.is_unique_constraint,
                    IX.is_unique
            FROM    sys.tables AS T
                    INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
                    INNER JOIN sys.indexes AS IX ON T.object_id = IX.object_id
                    INNER JOIN sys.index_columns AS IC ON IX.object_id = IC.object_id
                                                          AND IX.index_id = IC.index_id
                    INNER JOIN sys.columns AS C ON IC.column_id = C.column_id
                                                   AND IC.object_id = C.object_id
            WHERE   ( IX.is_unique = 1 )
                    AND ( T.name <> 'sysdiagrams' )
                    AND IX.is_unique = 1
            ORDER BY schema_name,
                    table_name,
                    index_name,
                    C.column_id
END



BEGIN -- Get a table full of FK columns

    DECLARE @foreign_key_columns TABLE
        (
          constraint_name NVARCHAR(128),
          base_schema_name NVARCHAR(128),
          base_table_name NVARCHAR(128),
          base_column_id INT,
          base_column_name NVARCHAR(128),
          unique_schema_name NVARCHAR(128),
          unique_table_name NVARCHAR(128),
          unique_column_id INT,
          unique_column_name NVARCHAR(128)
        )
    INSERT  INTO @foreign_key_columns
            (
              constraint_name,
              base_schema_name,
              base_table_name,
              base_column_id,
              base_column_name,
              unique_schema_name,
              unique_table_name,
              unique_column_id,
              unique_column_name
            )
            SELECT  FK.name AS constraint_name,
                    S.name AS base_schema_name,
                    T.name AS base_table_name,
                    C.column_id AS base_column_id,
                    C.name AS base_column_name,
                    US.name AS unique_schema_name,
                    UT.name AS unique_table_name,
                    UC.column_id AS unique_column_id,
                    UC.name AS unique_column_name
            FROM    sys.tables AS T
                    INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
                    INNER JOIN sys.foreign_keys AS FK ON T.object_id = FK.parent_object_id
                    INNER JOIN sys.foreign_key_columns AS FKC ON FK.object_id = FKC.constraint_object_id
                    INNER JOIN sys.columns AS C ON FKC.parent_object_id = C.object_id
                                                   AND FKC.parent_column_id = C.column_id
                    INNER JOIN sys.columns AS UC ON FKC.referenced_object_id = UC.object_id
                                                    AND FKC.referenced_column_id = UC.column_id
                    INNER JOIN sys.tables AS UT ON FKC.referenced_object_id = UT.object_id
                    INNER JOIN sys.schemas AS US ON UT.schema_id = US.schema_id
            WHERE   ( T.name <> 'sysdiagrams' )
            ORDER BY base_schema_name,
                    base_table_name
END

DECLARE @constraint_name NVARCHAR(128),
    @base_schema_name NVARCHAR(128),
    @base_table_name NVARCHAR(128),
    @unique_schema_name NVARCHAR(128),
    @unique_table_name NVARCHAR(128)

-- The foreign key side of the constraint is always singular, we need to check from the perspective
-- of the unique side of the constraint.

-- for each FK constraint in DB
DECLARE tmpC CURSOR READ_ONLY
    FOR SELECT DISTINCT
                constraint_name,
                base_schema_name,
                base_table_name,
                unique_schema_name,
                unique_table_name
        FROM    @foreign_key_columns

OPEN tmpC
FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
WHILE @@FETCH_STATUS = 0
    BEGIN
		-- get the columns in the base side of the FK constraint
        DECLARE @fkc TABLE
            (
              column_name NVARCHAR(128)
            )
        DELETE  FROM @fkc
        
        INSERT  INTO @fkc ( column_name )
                SELECT  base_column_name
                FROM    @foreign_key_columns
                WHERE   constraint_name = @constraint_name
		
		-- check for one to one/none
		-- If the base side columns of the constraint fit into any one of the base side tables unique constraints
		-- AND the column count is the same then we have a one-to-one/none and should be realized as a singular 
		-- object reference
		
		-- I realize that if the base side unique constraint has more columns than the unique side unique constraint
		-- AND all of those columns DO represent a 1:? that would actually qualify but it seems like an edge case and
		-- beyond the scope of this question.

        DECLARE @uk_schema_name NVARCHAR(128),
            @uk_table_name NVARCHAR(128),
            @uk_index_name NVARCHAR(128),
            @is_may_have_a BIT,
            @many_to_many_fk_schema NVARCHAR(128),
            @many_to_many_fk_name NVARCHAR(128),
            @many_to_many_fk_table_schema NVARCHAR(128),
            @many_to_many_fk_table_name NVARCHAR(128)
            
        SET @is_may_have_a = 0

		
		-- have to open another cursor over the unique keys of the base table - i want
		-- a distinct list of unique constraints for the base table
		
        DECLARE cKey CURSOR READ_ONLY
            FOR SELECT  DISTINCT
                        [schema_name],
                        table_name,
                        index_name
                FROM    @unique_key_columns
                WHERE   [schema_name] = @base_schema_name
                        AND table_name = @base_table_name

        OPEN cKey
        FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
        WHILE @@FETCH_STATUS = 0
            BEGIN
            
				-- get the unique constraint columns
                DECLARE @pkc TABLE
                    (
                      column_name NVARCHAR(128)
                    )
                DELETE  FROM @pkc
                
                INSERT  INTO @pkc ( column_name )
                        SELECT  column_name
                        FROM    @unique_key_columns
                        WHERE   [schema_name] = @uk_schema_name
                                AND table_name = @uk_table_name
                                AND index_name = @uk_index_name
				
				-- if count is same and columns are same
                DECLARE @count1 INT,
                    @count2 INT
                SELECT  @count2 = COUNT(*)
                FROM    @pkc
                
				-- select all from both on name and exclude mismatches
                SELECT  @count1 = COUNT(*)
                FROM    @fkc F
                        FULL OUTER JOIN @pkc P ON f.column_name = p.column_name
                WHERE   NOT p.column_name IS NULL
                        AND NOT f.column_name IS NULL 
                        
                IF @count1 = @count2 
                    BEGIN
						-- the base side of the fk constraint corresponds exactly to 
						-- at least on unique constraint making it effectively 1:?
                        SET @is_may_have_a = 1
                        BREAK
                    END
                 
                -- not 1:? - check for many-to-many 
				-- if the base side of the FK constraint fits into ONE of the base table's unique constraints but the unique constraint
				-- has more columns then it is a many-to-one (from the perspective of the unique side) and may be many-to-many relationship 
				-- that needs to be surfaced and the reference will be plural in both cases (e.g. intermediate records or related records)
				-- if the base side table consists solely of keys then it is definitely a many-to-many.
				-- if the base side has data fields as well as other FK constraints then it should be surfaced along with the related table.
				
				-- given the above lets first check to see if the FK will fit into the UT PK
				
                SELECT  @count1 = COUNT(*)
                FROM    @pkc P
                        full OUTER JOIN @fkc F ON f.column_name = p.column_name
                        WHERE NOT p.column_name IS NULL 

                                                
                IF @count1 != 0
					BEGIN -- the fk fits into the pk
						-- get the pk columns that are not in the fk and look for another fk
						DECLARE @opkc TABLE
							(
							  column_name NVARCHAR(128)
							)
						DELETE  FROM @opkc
						INSERT  INTO @opkc ( column_name )
						SELECT  p.column_name
						FROM    @pkc P
								full OUTER JOIN @fkc F ON f.column_name = p.column_name
								WHERE f.column_name IS NULL 
						
														
						-- find the fk constraints that contains the columns in @opkc and render a collection property for each
						DECLARE @mtmfk TABLE( many_to_many_fk_schema NVARCHAR(128),
							many_to_many_fk_name NVARCHAR(128),
							many_to_many_fk_table_schema NVARCHAR(128),
							many_to_many_fk_table_name NVARCHAR(128)
							)
						DELETE FROM @mtmfk
						
						INSERT INTO @mtmfk (many_to_many_fk_schema ,many_to_many_fk_name ,many_to_many_fk_table_schema ,many_to_many_fk_table_name )
						SELECT DISTINCT fkc.base_schema_name,constraint_name,fkc.unique_schema_name,fkc.unique_table_name
						FROM @foreign_key_columns fkc FULL OUTER JOIN @opkc o ON fkc.base_column_name = o.column_name
						WHERE fkc.base_schema_name=@base_schema_name AND fkc.base_table_name = @base_table_name AND NOT o.column_name IS NULL
						
						SELECT @count1 = COUNT(*) FROM @mtmfk 
						IF @count1 !=0
							BEGIN
								DECLARE cManyToManyFK CURSOR READ_ONLY FOR 
								SELECT many_to_many_fk_schema ,many_to_many_fk_name ,many_to_many_fk_table_schema ,many_to_many_fk_table_name FROM @mtmfk 
								OPEN cManyToManyFK
								FETCH NEXT FROM cManyToManyFK INTO @many_to_many_fk_schema ,@many_to_many_fk_name ,@many_to_many_fk_table_schema ,@many_to_many_fk_table_name 
								WHILE @@FETCH_STATUS = 0
									BEGIN
										-- is many to many - 
										-- TODO: check to see if the intermediate table is all keys, if not, it should be surfaced as many to one as well
										-- for now just surface it
										PRINT 'for ' + @unique_schema_name + '.' + @unique_table_name + ' constraint ' + +@constraint_name + ' is m:n with '  + @many_to_many_fk_table_name
										FETCH NEXT FROM cManyToManyFK INTO @many_to_many_fk_schema ,@many_to_many_fk_name ,@many_to_many_fk_table_schema ,@many_to_many_fk_table_name 
									END
								CLOSE cManyToManyFK
								DEALLOCATE cManyToManyFK
								-- SELECT @many_to_many_fk_schema=NULL,@many_to_many_fk_name =NULL, @many_to_many_fk_table_schema =NULL,@many_to_many_fk_table_name=NULL						
								-- run a cursor over the qualifying fk constraints and render a collection property. in most cases there will be only one.
								BREAK
							END
					END
                FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
            END
        CLOSE cKey
        DEALLOCATE cKey

        IF @is_may_have_a = 1 
            PRINT 'for ' + @unique_schema_name + '.' + @unique_table_name + ' constraint ' + +@constraint_name + ' is 1:? ' 
		
        FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
    END

CLOSE tmpC
DEALLOCATE tmpC

		-- (M2OChild->M2OParent, O2MParent->O2MChild)
		-- if NONE of the base table's unique constraints correspond to the base side of the FK constraint
		-- then it is a many-to-one and the reference is plural
		
		
		-- (M2MParentA->M2MInter, M2MUQParentA->M2MUQInter)
		-- if the base side of the FK constraint fits into ONE of the base table's unique constraints but the unique constraint
		-- has more columns then it is a many-to-one (from the perspective of the unique side) and may be many-to-many relationship 
		-- that needs to be surfaced and the reference will be plural in both cases (e.g. intermediate records or related records)
		-- if the base side table consists solely of keys then it is definitely a many-to-many.
		-- if the base side has data fields as well as other FK constraints then it should be surfaced along with the related table.
		
		-- it could be that the base side has a one-to-one with another table on the fk which would make a one-to-one here
		-- valid but that seems like a stretch and the target execution environment is a batch script and without recursion
		-- discovering these things is out of the scope of this task.
		
		-- unfortunately with this approach multiple 'inheritance' will not be recognized
		-- as it will look like a many-to-many intermediate relation - i am no sql guru but that
		-- seems like an extreme edge case to me so i am not going to worry about it